/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;


import com.informet.ipd.modelo.vo.DemandaVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
/**
 *
 * @author lover
 */
public class DemandaDao extends DBase {

Connection conn = null;

    public DemandaDao()  {
        super();
    }


    private void setListaDemanda(DemandaVO bean, ResultSet rs) throws SQLException {
        bean.setTX_DESCOBJ(rs.getString("TX_DESCOBJ"));
        bean.setTX_DESCOBJESP(rs.getString("TX_DESCOBJESP"));
        bean.setTX_DESCACTPERM(rs.getString("TX_DESCACTPER"));
        bean.setTX_DESCOBJ(rs.getString("TX_DESCOBJ"));
        bean.setTX_UMACTPER(rs.getString("TX_UMACTPER"));
        bean.setNUM_CANTACTPERM(rs.getInt("NUM_CANTACTPER"));
        bean.setTX_TIPOACTPERM(rs.getString("TX_TIPOACTPER"));
        bean.setNUM_PRESUACTPERM(rs.getBigDecimal("NUM_PRESUACTPER"));
        bean.setNUM_CODMETAAPERM_PK(rs.getInt("NUM_CODMETAAPER_PK"));
        bean.setNUM_CODACTPERM_PK(rs.getInt("NUM_CODACTPER_PK"));
    }
    
    private void setmaximo(DemandaVO bean, ResultSet rs) throws SQLException {
        bean.setMaximo(rs.getInt("maximo"));
       //bean.setTX_RESPONACTPER(rs.getString("TX_RESPONACTPER"));
        //bean.setTX_DESCACTPER(rs.getString("TX_DESCACTPER"));
    }


    public int maximo() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DemandaVO> lista = new ArrayList<DemandaVO>(0);
        String sql = "";
        int max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select max(NUM_CODACTPERM_PK) as maximo from ME_ACTPERM";
            rs = stmt.executeQuery(sql);
            DemandaVO bean = null;
            while (rs.next()) {
                bean = new DemandaVO();
                setmaximo(bean, rs);
                max=bean.getMaximo();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }


    public List ListaDemanda(int id,int id2) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<DemandaVO> lista = new ArrayList<DemandaVO>(0);
        String sql = "";
        //JOptionPane.showMessageDialog(null,"id"+ id);
        //JOptionPane.showMessageDialog(null,"id2"+ id2);
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select   o.TX_DESCOBJ,OE.TX_DESCOBJESP ,  ac.TX_DESCACTPER,isnull(mac.TX_UMACTPER,'') as TX_UMACTPER,isnull(mac.NUM_CANTACTPER,0) as NUM_CANTACTPER,isnull(mac.TX_TIPOACTPER,'') as TX_TIPOACTPER,mac.NUM_CODMETAAPER_PK,ac.NUM_PRESUACTPER,ac.NUM_CODACTPER_PK from dbo.ME_ACTPER ac inner join dbo.ME_METAAPER mac  on ac.NUM_CODACTPER_PK=mac.NUM_CODACTPER_PK inner join ME_OBJESP oe  on  oe.NUM_CODOBJESP_PK= ac.NUM_CODOBJESP_PK  inner join ME_OBJ o  on  o.NUM_CODOBJ_PK=oe.NUM_CODOBJ_PK   inner join ME_DEPENDENCIA d on d.NUM_CODDEPEN_PK=ac.NUM_CODDEPEN_PK where ac.NUM_CODACTPER_PK=? and d.NUM_CODDEPEN_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.setInt(2, id2);
            rs = pstmt.executeQuery();
            DemandaVO bean = null;
            while (rs.next()) {
                bean = new DemandaVO();
                setListaDemanda(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }





    public String spMan(int tipo, DemandaVO bean) {
        String msj = "";
        try {
            //JOptionPane.showMessageDialog(null,"tipo"+ tipo);
            //JOptionPane.showMessageDialog(null,"descr. aperm"+ bean.getTX_DESCACTPERM());
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_METAAPERMOD(?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODMETAAPERM_PK());
            cs.setString(3, bean.getTX_UMACTPERM());
            cs.setDouble(4, bean.getNUM_CANTACTPERM());
            cs.setString(5, bean.getTX_TIPOACTPERM());
            cs.setInt(6, bean.getNUM_CODACTPERM_PK());
            cs.setBigDecimal(7, bean.getNUM_PRESUACTPERM());
            cs.setString(8, bean.getTX_DESCACTPERM());
            cs.execute();
        } catch (Exception e) {
            msj = e.toString();
            //JOptionPane.showMessageDialog(null,"error"+ msj);
            return msj;
        }
        return msj;
    }




    public String spManm(int tipo, DemandaVO bean) {
        String msj = "";
        try {
            //JOptionPane.showMessageDialog(null,"tipo"+ tipo);
            //JOptionPane.showMessageDialog(null,"descr. aperm"+ bean.getTX_DESCACTPERM());
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_APERMODI(?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setString(2, bean.getTX_DESCACTPERM());
            cs.setString(3, bean.getTX_UMACTPERM());
            cs.setInt(4, bean.getNUM_CANTACTPERM());
            cs.setString(5, bean.getTX_TIPOACTPERM());
            cs.setInt(6, bean.getCOD_OBJESP());
            cs.setInt(7, bean.getNUM_CODDEPEN_PK());
            cs.setInt(8, 0);
            cs.execute();
        } catch (Exception e) {
            msj = e.toString();
            //JOptionPane.showMessageDialog(null,"error"+ msj);
            return msj;
        }
        return msj;
    }

    public String spManmo(int tipo, DemandaVO bean) {
        String msj = "";
        try {

            
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_METAAPERMODI(?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setString(2, bean.getTX_UMACTPERM());
            cs.setInt(3, bean.getNUM_CANTACTPERM());
            cs.setString(4, bean.getTX_TIPOACTPERM());
            cs.setInt(5, bean.getNUM_CODACTPERM_PK());
            cs.setInt(6, bean.getNUM_CODMETAAPERM_PK());
            cs.execute();
        } catch (Exception e) {
            msj = e.toString();
            //JOptionPane.showMessageDialog(null,"error"+ msj);
            return msj;
        }
        return msj;
    }



    










}
